package com.agv.core.dao;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;

import com.agv.core.dao.dataExtractor.GenericRowMapper;
import com.agv.core.dao.dataExtractor.InfoAuditoriaResultSetExtractor;
import com.agv.core.dao.dataExtractor.MenuSelectRowMapper;
import com.agv.core.dao.dataExtractor.TableTypeResultSetExtractor;
import com.agv.core.exception.AgvException;
import com.agv.core.objectDomain.AdmCatalogo;
import com.agv.core.objectDomain.Catalogo;
import com.agv.core.objectDomain.InfoAuditoria;
import com.agv.core.objectDomain.MenuSelect;
import com.agv.core.objectDomain.ParamCatalogo;
import com.agv.core.util.Constantes;
import com.agv.core.util.ListUtil;

public class CatalogosDaoImpl implements ICatalogosDao {

	private JdbcTemplate jdbcTemplate;

	@SuppressWarnings("unchecked")
	public ArrayList<MenuSelect> obtenerMenuSelect(String nbCampoLeyenda,
			String nbCampoId, String tabla) throws AgvException {
		String query = "SELECT " + nbCampoLeyenda + " as leyenda," + nbCampoId
				+ " as id FROM " + tabla;
		ArrayList<MenuSelect> lista = (ArrayList<MenuSelect>) jdbcTemplate
				.query(query, new MenuSelectRowMapper());
		return lista;
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, String>> getCombo(String query) throws AgvException {
		return jdbcTemplate.query(query, new GenericRowMapper());
	}

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate
	 *            the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public void insertarCatalgoBD(AdmCatalogo admCatalogo, Integer idUsuarioAlta)
			throws AgvException {

		Integer idPrincipalValue = 0;
		String idPrincipalField = "";
		int counter = 0;
		for (Catalogo catalogo : admCatalogo.getCatalogos()) {

			String query = "INSERT INTO " + catalogo.getNbTabla() + " ";
			String qCampos = "";
			String qValores = "";

			if (admCatalogo.getTpInsercionCatalogo().equals(
					Constantes.INSERCION_ANIDADA)) {
				if (counter == 0) {
					idPrincipalValue = jdbcTemplate
							.queryForInt(" select nextval('"
									+ catalogo.getNbSecuenciaId() + "')");
					idPrincipalField = catalogo.getNbCampoId();
					qCampos = catalogo.getNbCampoId() + ",";
					qValores = idPrincipalValue + ",";
				} else if (counter == 1
						&& admCatalogo.getCatalogos().size() >= 2) {
					qCampos = catalogo.getNbCampoId() + "," + idPrincipalField
							+ ",";
					qValores = "nextval('" + catalogo.getNbSecuenciaId()
							+ "')," + idPrincipalValue + ",";
				}
			} else {
				qCampos = catalogo.getNbCampoId() + ",";
				qValores = "nextval('" + catalogo.getNbSecuenciaId() + "'),";
			}

			for (ParamCatalogo params : catalogo.getParams()) {
				qCampos += params.getNbCampoTabla() + ",";
				qValores += "'" + params.getVlValue() + "',";
			}

			qCampos = qCampos.substring(0, qCampos.length() - 1);

			qCampos += ",id_usuario_act_ant,fecha_act_ant,id_usuario_ult_act,fecha_ult_act";

			qValores = qValores.substring(0, qValores.length() - 1);
			qValores += "," + idUsuarioAlta + ",'"
					+ new Timestamp(System.currentTimeMillis()).toString()
					+ "'" + "," + idUsuarioAlta + ",'"
					+ new Timestamp(System.currentTimeMillis()).toString()
					+ "'";

			if(qCampos.indexOf("activo") < 0){
				qCampos += ",activo";
				qValores += ",1";
			}

			qCampos = "(" + qCampos + ")";
			qValores = "(" + qValores + ")";

			query = query + qCampos + " VALUES " + qValores;
			jdbcTemplate.execute(query);
			counter++;
		}

	}

	@SuppressWarnings("unchecked")
	@Override
	public ListUtil obtenerListaAutocompletar(String nbCampoLeyenda,
			String tabla) throws AgvException {
		String query = "select " + nbCampoLeyenda + " from " + tabla
				+ " where activo <> 3";
		ArrayList<String> lista = (ArrayList<String>) jdbcTemplate
				.queryForList(query, String.class);

		ListUtil listUtil = new ListUtil();
		for (String p : lista) {
			listUtil.add(p.trim());
		}

		return listUtil;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, String>> buscar(String vlValor, String nbTabla,
			String nbCampo, String nbCampoId,
			ArrayList<ParamCatalogo> parametros) throws AgvException {
		String nbCampos = "";
		for (ParamCatalogo param : parametros) {
			nbCampos += param.getNbCampoTabla() + ",";
		}
		nbCampos = nbCampoId + ","
				+ nbCampos.substring(0, nbCampos.length() - 1);
		String query = "select " + nbCampos + " from " + nbTabla + " where "
				+ nbCampo + " like '%" + vlValor + "%'" + " and activo <> 3";

		List<Map<String, String>> map = jdbcTemplate.query(query,
				new GenericRowMapper());

		return map;
	}
	
	
	
	@SuppressWarnings("unchecked")
	public List<Map<String, String>> busquedaGeneral(String query,
			Object[] parametros) throws AgvException {

		List<Map<String, String>> map = jdbcTemplate.query(query, parametros,
				new GenericRowMapper());

		return map;
	}

	@SuppressWarnings("unchecked")
	public Map<String, Integer> buscarModelTabla(String query)
			throws AgvException {

		Map<String, Integer> map = (Map<String, Integer>) jdbcTemplate.query(
				query, new TableTypeResultSetExtractor());

		return map;
	}

	@Override
	public InfoAuditoria consultaInfoAuditoria(String nbTabla,
			String nbCampoId, String vlValorId) throws AgvException {
		String query = "select a.nb_usuario as nb_usuario, b.tm_alta as th "
				+ " from empresa.emp006_cfg_usuarios a, " + nbTabla + " b "
				+ " where b." + nbCampoId + " = " + vlValorId
				+ " and b.id_usuario_alta = a.id_usuario " + " union "
				+ " select a.nb_usuario as nb_usuario_mod, b.tm_mod "
				+ " from empresa.emp006_cfg_usuarios a, " + nbTabla + " b"
				+ " where b." + nbCampoId + " = " + vlValorId
				+ " and b.id_usuario_mod = a.id_usuario ";
		InfoAuditoria infoAuditoria = (InfoAuditoria) jdbcTemplate.query(query,
				new InfoAuditoriaResultSetExtractor());
		return infoAuditoria;
	}

	@Override
	public void actualizaCatalgoBD(AdmCatalogo admCatalogo,
			Integer idUsuarioMod, Integer vlCampoId) throws AgvException {
		for (Catalogo catalogo : admCatalogo.getCatalogos()) {
			String query = "update " + catalogo.getNbTabla() + " set ";
			String qCampos = "";

			for (ParamCatalogo params : catalogo.getParams()) {
				qCampos += params.getNbCampoTabla() + " = '"
						+ params.getVlValue() + "',";
			}

			qCampos = qCampos.substring(0, qCampos.length() - 1);

			qCampos += ",id_usuario_mod = " + idUsuarioMod + ",tm_mod = '"
					+ new Timestamp(System.currentTimeMillis()).toString()
					+ "'";

			query = query + qCampos + " where " + catalogo.getNbCampoId()
					+ " = " + vlCampoId;
			jdbcTemplate.execute(query);
		}
	}

	@Override
	public void borrarCatalgoBD(AdmCatalogo admCatalogo, Integer idUsuarioMod,
			Integer vlCampoId) throws AgvException {
		for (Catalogo catalogo : admCatalogo.getCatalogos()) {
			String query = "update " + catalogo.getNbTabla() + " set ";
			String qCampos = " activo = 3";

			qCampos += ",id_usuario_mod = " + idUsuarioMod + ",tm_mod = '"
					+ new Timestamp(System.currentTimeMillis()).toString()
					+ "'";

			query = query + qCampos + " where " + catalogo.getNbCampoId()
					+ " = " + vlCampoId;

			jdbcTemplate.execute(query);
		}
	}

	@Override
	public boolean validaDuplicidad(String nbCampo, String vlValor,
			String nbTabla) throws AgvException {
		String query = "select count(" + nbCampo + ") from " + nbTabla
				+ " where " + nbCampo + " = '" + vlValor + "' ";
		Integer nuRegistros = jdbcTemplate.queryForInt(query);
		if (nuRegistros > 0) {
			return true;
		} else {
			return false;
		}
	}

	@SuppressWarnings("unchecked")
	public void addConfiguration(Map<String, Object> params)
			throws AgvException {
		List<Map<String, String>> catParams = (List<Map<String, String>>) params
				.get("catParams");

		Map<String, String> confParams = (Map<String, String>) params
				.get("confParams");

		Integer idCatalog = jdbcTemplate
				.queryForInt(" select nextval('adm.adm001_cfg_catalogos_seq')");

		String insertConf = "insert into adm.adm001_cfg_catalogos (id_catalogo,nb_catalogo,nb_tabla,nb_secuencia) values (?,?,?,?)";

		int a = jdbcTemplate.update(insertConf, new Object[] { idCatalog,
				confParams.get("title"),
				confParams.get("schema") + "." + confParams.get("table"),
				confParams.get("sequence") });

		for (Map<String, String> catParam : catParams) {
			if (catParam.get("isId").equals("true")) {
				a = jdbcTemplate
						.update(
								"update adm.adm001_cfg_catalogos set nb_campoid = ? where id_catalogo = ?",
								new Object[] { catParam.get("name"), idCatalog });
			}

			if (catParam.get("isAutoComplete").equals("true")) {
				a = jdbcTemplate
						.update(
								"update adm.adm001_cfg_catalogos set nb_campoautocompletar = ? where id_catalogo = ?",
								new Object[] { catParam.get("name"), idCatalog });
			}

			Integer idParam = jdbcTemplate
					.queryForInt(" select nextval('adm.adm002_cfg_param_catalogo_seq')");

			String insertParam = "insert into adm.adm002_cfg_param_catalogo (id_param,nu_longitud,tp_validacion,tp_dato,"
					+ " nb_campo_tabla,nb_campo_alias,nb_cat_campo_id,nb_cat_campo_leyenda,nb_cat_tabla,id_catalogo,regex,req,val_dup )"
					+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?)";

			a = jdbcTemplate.update(insertParam, new Object[] {
					idParam,
					catParam.get("dataLenght").equals("") ? null : new Integer(
							catParam.get("dataLenght")),
					catParam.get("valtypes"), catParam.get("type"),
					catParam.get("name"), catParam.get("alias"),
					catParam.get("nbField"), catParam.get("nbLegend"),
					catParam.get("nbTable"), idCatalog,
					catParam.get("regexVal"),
					new Boolean(catParam.get("isReq")),
					new Boolean(catParam.get("isDupVal")) });
		}

	}

	@SuppressWarnings("unchecked")
	public void addStepConfiguration(Map<String, Object> params)
			throws AgvException {
		List<Map<String, String>> catParams = (List<Map<String, String>>) params
				.get("stepParams");

		Map<String, String> confParams = (Map<String, String>) params
				.get("confParams");

		Integer idStepConf = jdbcTemplate
				.queryForInt(" select nextval('adm.adm004_cfg_cat_seq_s')");

		String insertConf = "insert into adm.adm004_cfg_cat_seq (id_seq_conf,nb_title,is_anidate,id_module,id_submodule,"
				+ "id_fucntion,seq_conf) values (?,?,?,?,?,?,?)";

		String seqStr = "";
		for (Map<String, String> param : catParams) {
			seqStr += param.get("name")
					+ (param.get("isReq").equals("true") ? "*," : "+,");
		}

		seqStr = seqStr.substring(0, seqStr.length() - 1);

		int a = jdbcTemplate.update(insertConf, new Object[] { idStepConf,
				confParams.get("title"),
				new Boolean(confParams.get("isAnidate")),
				new Integer(confParams.get("module")),
				new Integer(confParams.get("subModule")),
				new Integer(confParams.get("function")), seqStr });

		a = jdbcTemplate
				.update(
						"update empresa.emp010_funcion set nb_ruta = ? where id_modulo = ? and id_submodulo = ? and id_funcion = ?",
						new Object[] { idStepConf,
								new Integer(confParams.get("module")),
								new Integer(confParams.get("subModule")),
								new Integer(confParams.get("function")) });

	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, String> getCatConfiguration(Integer idCat)
			throws AgvException {
		return jdbcTemplate
				.queryForMap(
						"select trim(both ' ' from nb_title) as nb_title,trim(both ' ' from seq_conf) as seq_conf,is_anidate"
								+ " from adm.adm004_cfg_cat_seq where id_seq_conf = ?",
						new Object[] { idCat });
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Map<String, String>> getCatSteps(String[] idCats)
			throws AgvException {

		String param = "";
		for (String p : idCats) {
			param += p + ",";
		}
		param = param.substring(0, param.length() - 1);

		return jdbcTemplate
				.query(
						"select id_catalogo,nb_catalogo,nb_tabla,nb_campoautocompletar,nb_campoid,nb_secuencia,tp_borrado "
								+ " from adm.adm001_cfg_catalogos where id_catalogo in ("
								+ param + ")", new GenericRowMapper());
	}

}
